package com.cloudsea.yunhaianalysis.mapper;

import com.cloudsea.yunhaianalysis.entity.SalesAnalysis;
import com.cloudsea.yunhaianalysis.vo.BusinessHoursVO;
import com.cloudsea.yunhaianalysis.vo.SalesTrendVO;
import com.cloudsea.yunhaianalysis.vo.TopSellingItemVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;

@Mapper
public interface AnalysisMapper {

    /**
     * 查询指定时间范围内的热销商品SKU排行
     * @param storeId 门店ID
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param topN 需要返回的排行数量
     * @return 热销商品列表
     */
    @Select("SELECT " +
            "   oi.sku_id as skuId, " +
            "   oi.spu_name as spuName, " +
            "   oi.sku_name as skuName, " +
            "   SUM(oi.quantity) as totalQuantity, " +
            "   SUM(oi.sku_price * oi.quantity) as totalAmount " +
            "FROM order_items oi " +
            "JOIN payment_orders po ON oi.order_id = po.id " +
            "WHERE po.store_id = #{storeId} " +
            "  AND po.pay_status = 1 " + // 只统计已支付的订单
            "  AND po.created_at BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY oi.sku_id, oi.spu_name, oi.sku_name " +
            "ORDER BY totalQuantity DESC " + // 按销量排行
            "LIMIT #{topN}")
    List<TopSellingItemVO> getTopSellingItems(@Param("storeId") Long storeId,
                                              @Param("startTime") LocalDateTime startTime,
                                              @Param("endTime") LocalDateTime endTime,
                                              @Param("topN") Integer topN);



    /**
     * 查询指定时间范围内的每日销售趋势数据
     * @param storeId 门店ID
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 每日销售数据列表
     */
    @Select("SELECT " +
            "   DATE(po.created_at) AS date, " +
            "   COALESCE(SUM(po.total_amount), 0) AS turnover, " +
            "   COALESCE(COUNT(po.id), 0) AS orderCount " +
            "FROM payment_orders po " +
            "WHERE po.store_id = #{storeId} " +
            "  AND po.pay_status = 1 " + // 只统计已支付的订单
            "  AND po.created_at BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY DATE(po.created_at) " +
            "ORDER BY date ASC")
    List<SalesTrendVO> getSalesTrend(@Param("storeId") Long storeId,
                                     @Param("startTime") LocalDateTime startTime,
                                     @Param("endTime") LocalDateTime endTime);

    /**
     * 查询指定时间范围内的分时营业数据
     * @param storeId 门店ID
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 分时营业数据列表
     */
    @Select("SELECT " +
            "   HOUR(po.created_at) AS hour, " +
            "   COALESCE(COUNT(po.id), 0) AS orderCount, " +
            "   COALESCE(SUM(po.total_amount), 0) AS turnover " +
            "FROM payment_orders po " +
            "WHERE po.store_id = #{storeId} " +
            "  AND po.pay_status = 1 " +
            "  AND po.created_at BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY HOUR(po.created_at) " +
            "ORDER BY hour ASC")
    List<BusinessHoursVO> getBusinessHours(@Param("storeId") Long storeId,
                                           @Param("startTime") LocalDateTime startTime,
                                           @Param("endTime") LocalDateTime endTime);

    // ...
    /**
     * 【新增】按SKU聚合指定日期的销售数据
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 聚合后的销售数据列表
     */
    @Select("SELECT " +
            "   po.store_id as storeId, " +
            "   oi.sku_id as skuId, " +
            "   SUM(oi.quantity) as quantitySold, " +
            "   SUM(oi.sku_price * oi.quantity) as totalAmount " +
            "FROM order_items oi " +
            "JOIN payment_orders po ON oi.order_id = po.id " +
            "WHERE po.pay_status = 1 " + // 只统计已支付的订单
            "  AND po.created_at BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY po.store_id, oi.sku_id")
    List<SalesAnalysis> summarizeDailySales(@Param("startTime") LocalDateTime startTime,
                                            @Param("endTime") LocalDateTime endTime);
}
